﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.SqlClient;
using System.IO;

public partial class T_InquiryPage : System.Web.UI.Page
{
    public string studentid; //用于保存表格修改之前的id，以便表格修改之后在数据库中找到相应行
    public static DataTable ta = new DataTable();//用于存储从数据库中取出来的表格，可以在多个函数中使用，所以使用static
    public static DataView vi = new DataView();  //用于数据绑定
    public static string accnum;           //登录账号
    public static string role;             //登录身份

    protected void Page_Load(object sender, EventArgs e)
    {
        btnFirst.Text = "最首页";    //分页处理
        btnPrev.Text = "前一页";
        btnNext.Text = "下一页";
        btnLast.Text = "最后页";
      
        if (!IsPostBack)
        {
            accnum=(string)Session["AccNum"];
            role=(string)Session["role"];
            ShowDataList();
            /*
            DataSet m_ds = new DataSet();
            SqlConnection myConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=CSDB");
            SqlCommand cmd = new SqlCommand("select courseid,COURSE_INFO.name as coursename,studentid,STU_INFO.name as studentname,mark,SEM_INFO.name as semester from SEM_INFO,CLASS_INFO,COURSE_INFO,STU_INFO where CLASS_INFO.courseid=COURSE_INFO.id and CLASS_INFO.studentid=STU_INFO.id and CLASS_INFO.semesterid=SEM_INFO.id;", myConn);
            myConn.Open();

            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;
            da.Fill(m_ds, "student");
            ta = m_ds.Tables["student"];   //保存表格到ta中
            dg1.DataSource = m_ds.Tables["student"].DefaultView;
            dg1.DataBind();
            myConn.Close();
            ViewState.Add("ds", m_ds);  //viesstate保存数据集m_ds
            */
            try
            {
                Execute("select courseid,COURSE_INFO.name as coursename,studentid,STU_INFO.name as studentname,mark,SEM_INFO.name as semester from SEM_INFO,CLASS_INFO,COURSE_INFO,STU_INFO where CLASS_INFO.courseid=COURSE_INFO.id and COURSE_INFO.teacherid='" + accnum + "' and CLASS_INFO.studentid=STU_INFO.id and CLASS_INFO.semesterid=SEM_INFO.id and CLASS_INFO.state='1';");
                //执行查询语句，默认状态下加载页面时显示浏览选课信息，此处还没有根据登录教师id确定响应选课信息，而是选定整个COURSE_INFO表，需做进一步处理
            }
            catch
            { 
            
            }
        }
    }

    private void ShowStats()  //用于分页处理
    {
        lblCurrentIndex.Text = "第 " + (dg1.CurrentPageIndex + 1).ToString() + " 页";
        lblPageCount.Text = "总共 " + dg1.PageCount.ToString() + " 页";
    }

    public void PagerButtonClick(object sender, EventArgs e) //用于分页处理
    {
        string arg = ((LinkButton)sender).CommandArgument.ToString();
        switch (arg)
        {
            case "next":
                if (dg1.CurrentPageIndex < (dg1.PageCount - 1))
                {
                    dg1.CurrentPageIndex += 1;
                }
                break;
            case "prev":
                if (dg1.CurrentPageIndex > 0)
                {
                    dg1.CurrentPageIndex -= 1;
                }
                break;
            case "last":
                dg1.CurrentPageIndex = (dg1.PageCount - 1);
                break;
            default:
                dg1.CurrentPageIndex = System.Convert.ToInt32(arg);
                break;
        }
        vi = new DataView(ta);

        dg1.DataSource = vi;
        dg1.DataBind();
        ShowStats();
    }

    public void MyDataGrid_Page(Object source, DataGridPageChangedEventArgs e)  //用于分页处理
    {
        int startIndex;
        startIndex = dg1.CurrentPageIndex * dg1.PageSize;
        dg1.CurrentPageIndex = e.NewPageIndex;
        //BindGrid();
        vi = new DataView(ta);

        dg1.DataSource = vi;
        dg1.DataBind();
        ShowStats();

    }
    protected void LinkButton2_Click(object sender, EventArgs e)//浏览登录教师个人基本信息
    {

        ShowDataList();
        dg1.CurrentPageIndex = 0;

        /*
        DataSet m_ds = new DataSet();
        SqlConnection myConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=CSDB");
        SqlCommand cmd = new SqlCommand("SELECT * FROM TEA_INFO", myConn);
        myConn.Open();

        SqlDataAdapter da = new SqlDataAdapter();
        da.SelectCommand = cmd;
        da.Fill(m_ds, "teacher");
        ta = m_ds.Tables["teacher"];   //保存表格到ta中

        dg1.DataSource = m_ds.Tables["teacher"].DefaultView;
        dg1.DataBind();
        myConn.Close();
         */
        try
        {
            Execute("SELECT * FROM TEA_INFO where id='" + accnum + "';");//查询语句未对登录教师账号进行处理，需进一步处理
        }
        catch
        { 
        
        }

    }
    protected void LinkButton1_Click(object sender, EventArgs e)//浏览选课信息，尚未完成
    {

        ShowDataList();
        dg1.CurrentPageIndex = 0;

        /*
        DataSet m_ds = new DataSet();
        SqlConnection myConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=CSDB");
        SqlCommand cmd = new SqlCommand("select courseid,COURSE_INFO.name as coursename,studentid,STU_INFO.name as studentname,mark,SEM_INFO.name as semester from SEM_INFO,CLASS_INFO,COURSE_INFO,STU_INFO where CLASS_INFO.courseid=COURSE_INFO.id and CLASS_INFO.studentid=STU_INFO.id and CLASS_INFO.semesterid=SEM_INFO.id;", myConn);
        myConn.Open();

        SqlDataAdapter da = new SqlDataAdapter();
        da.SelectCommand = cmd;
        da.Fill(m_ds, "classinfo");
        ta = m_ds.Tables["classinfo"];   //保存表格到ta中
        dg1.DataSource = m_ds.Tables["classinfo"].DefaultView;
        dg1.DataBind();
        myConn.Close();
         */
        try
        {
            Execute("select courseid,COURSE_INFO.name as coursename,studentid,STU_INFO.name as studentname,mark,SEM_INFO.name as semester from SEM_INFO,CLASS_INFO,COURSE_INFO,STU_INFO where CLASS_INFO.courseid=COURSE_INFO.id and COURSE_INFO.teacherid='" + accnum + "' and CLASS_INFO.studentid=STU_INFO.id and CLASS_INFO.semesterid=SEM_INFO.id and CLASS_INFO.state='1';");
            //尚未针对登录教师账号进行处理
        }
        catch
        { 
        
        }
    }

    protected void LinkButton3_Click(object sender, EventArgs e)
    {
        HideDataList1();
        dg1.CurrentPageIndex = 0;

        DL1.Text = "请选择查询信息";
        DL2.Text = "请选择学期";
        Label1.Text = "输入信息";
        TextBox1.Text = "";
    }
    protected void LinkButton4_Click(object sender, EventArgs e)
    {
        HideDataList2();
        dg1.CurrentPageIndex = 0;

        DL3.Text = "请选择学期";
        DL4.Text = "请选择查询方式";
        DL5.Text = "请选择班级";
        TextBox2.Text = "";
        TextBox3.Text = "";
    }
    public void ShowDataList()
    {
        dg1.Visible = true;
        btnFirst.Visible = true;
        btnPrev.Visible =true;
        btnNext.Visible = true;
        btnLast.Visible = true;
        lblPageCount.Visible = true;
        lblCurrentIndex.Visible = true;
        DL1.Visible = false;
        DL2.Visible = false;
        Label1.Visible = false;
        TextBox1.Visible = false;
        Button1.Visible = false;

        DL3.Visible = false;
        DL4.Visible = false;
        DL5.Visible = false;
        Label2.Visible = false;
        TextBox2.Visible = false;
        Label3.Visible = false;
        TextBox3.Visible = false;
        Button2.Visible = false;

    }
    public void HideDataList1()
    {
        dg1.Visible = false;
        btnFirst.Visible = false;
        btnPrev.Visible = false;
        btnNext.Visible = false;
        btnLast.Visible = false;
        lblPageCount.Visible=false;
        lblCurrentIndex.Visible = false;
        DL1.Visible = true;
        DL2.Visible = false;
        Label1.Visible = true;
        TextBox1.Visible = true;
        Button1.Visible = true;
        DL3.Visible = false;
        DL4.Visible = false;
        DL5.Visible = false;
        Label2.Visible = false;
        TextBox2.Visible = false;
        Label3.Visible = false;
        TextBox3.Visible = false;
        Button2.Visible = false;

    }
    public void HideDataList2()
    {
        dg1.Visible = false;
        btnFirst.Visible = false;
        btnPrev.Visible = false;
        btnNext.Visible = false;
        btnLast.Visible = false;
        lblPageCount.Visible = false;
        lblCurrentIndex.Visible = false;
        DL1.Visible = false;
        DL2.Visible = false;
        Label1.Visible = false;
        TextBox1.Visible = false;
        Button1.Visible =false;

        DL3.Visible = true;
        DL4.Visible = true;
        DL5.Visible =false;
        Label2.Visible = true;
        TextBox2.Visible = true;
        Label3.Visible = false;
        TextBox3.Visible = false;
        Button2.Visible = true;

    }
    protected void DL1_TextChanged(object sender, EventArgs e)
    {
        DL2.Visible=false;
　　　　if (DL1.Text == "请选择查询信息")
        {
            DL2.Visible=false;
            Label1.Text = "输入信息";
        }
        if (DL1.Text == "账号查询")
        {
            DL2.Visible=false;
            Label1.Text = "请输入学生账号";
        }
        else if (DL1.Text == "课程名查询")
        {
            DL2.Visible=true;
            Label1.Text = "请输入课程名";

        }
 
    }

    protected void DL4_TextChanged(object sender, EventArgs e)
    {
        DL5.Visible = false;
        Label3.Visible = false;
        TextBox3.Visible = false;
        if (DL4.Text == "详细查询")
        { 
           
        }
        if (DL4.Text == "班级查询")
        {
            DL5.Visible = true;
        }
        if (DL4.Text == "账号查询")
        {
            Label3.Visible = true;
            TextBox3.Visible = true;
        }
    }

    protected void Button1_Click(object sender, EventArgs e)//缺少登录教师号的确定，缺少数据不存在，返回空表的提示
    {
        string sql = "";

        if (DL1.Text == "请选择查询信息")
        {
            Alert("请选择查询方式");
        }
        else if(DL1.Text=="账号查询")
        {
            if (TextBox1.Text == "")
            {
                Alert("请输入学生账号");
            }
            else
            {
                
                sql = "SELECT STU_INFO.id as studentid,STU_INFO.name,gender,COL_INFO.name as collegename,speciality,class,phone,email from STU_INFO,COL_INFO where STU_INFO.collegeid=COL_INFO.id and STU_INFO.state='1' and STU_INFO.id=" + TextBox1.Text;
                try
                {
                    if (Execute(sql) != -1)
                    {
                        ShowDataList();
                    }
                    else
                    {
                        Alert("查询账号不存在");
                    }
                }
                catch
                { 
                
                }
            }
        }
        else if(DL1.Text=="课程名查询")
        {
           if(DL2.Text=="请选择学期")
           {
               Alert("请选择学期");
           }
           else
           {
              if(TextBox1.Text=="")
              {
                  Alert("请输入课程名");
              }
              else
              {
                 
                  sql = "SELECT DISTINCT COURSE_INFO.id as courseid,COURSE_INFO.name as coursename,COL_INFO.name as collegename,TEA_INFO.name as teachername,time,room,credit,SEM_INFO.name as semester FROM COURSE_INFO,CLASS_INFO,SEM_INFO,COL_INFO,TEA_INFO WHERE CLASS_INFO.courseid=COURSE_INFO.id AND COURSE_INFO.name='" + TextBox1.Text + "' and SEM_INFO.id=CLASS_INFO.semesterid and SEM_INFO.name='" + DL2.Text + "' and COURSE_INFO.collegeid=COL_INFO.id and COURSE_INFO.teacherid=TEA_INFO.id and COURSE_INFO.state='1';";
                  try
                  {
                      if (Execute(sql) != -1)
                      {
                          ShowDataList();
                      }
                      else
                      {
                          Alert("查询课程不存在");
                      }
                  }
                  catch
                  { 
                  
                  }
              }

           }
        }

        

    }

    int Execute(string sql)//缺少查询信息不存在的出错处理，即返回空表，（上面的执行语句都可用此函数替代）
    {
        SqlConnection myConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=CSDB");
        SqlCommand cmd = new SqlCommand(sql, myConn);
        myConn.Open();

        SqlDataAdapter da = new SqlDataAdapter();
        da.SelectCommand = cmd;
        ta.Columns.Clear();
        ta.Rows.Clear();
        da.Fill(ta);
        if (ta.Rows.Count == 0)
        {
            myConn.Close();
            return -1;
        }
        else
        {
            vi = new DataView(ta);
            dg1.DataSource = vi;
            dg1.DataBind();

            myConn.Close();
            return 0;
        }
    }

    protected void Button2_Click(object sender, EventArgs e)//缺少登录教师号的确定
    {
        /*
        ShowDataList();
        DataSet m_ds = new DataSet();
        SqlConnection myConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=CSDB");
        SqlCommand cmd = new SqlCommand("SELECT * FROM TEA_INFO", myConn);
        myConn.Open();

        SqlDataAdapter da = new SqlDataAdapter();
        da.SelectCommand = cmd;
        da.Fill(m_ds, "Tinfo");
        ta = m_ds.Tables["Tinfo"];   //保存表格到ta中
        dg1.DataSource = m_ds.Tables["Tinfo"].DefaultView;
        dg1.DataBind();
        myConn.Close();
        */
        string sql = "";
        if (DL3.Text == "请选择学期")
        {
            Alert("请选择学期");
        }
        else
        {
            if (DL4.Text == "请选择查询方式")
            {
                Alert("请选择查询方式");
            }
            else if(DL4.Text == "详细查询")//已实现查询名次
            {
                if (TextBox2.Text == "")
                {
                    Alert("请输入课程名");
                }
                else
                {
                    
                    sql = "select identity(int,1,1) as rank,courseid,COURSE_INFO.name as coursename,studentid,STU_INFO.name as studentname,mark,SEM_INFO.name as semester into #aa from SEM_INFO,CLASS_INFO,COURSE_INFO,STU_INFO where CLASS_INFO.courseid=COURSE_INFO.id COURSE_INFO.teacherid='"+accnum+"' and CLASS_INFO.studentid=STU_INFO.id and CLASS_INFO.semesterid=SEM_INFO.id and CLASS_INFO.state='1' and SEM_INFO.name='" + DL3.Text + "' and COURSE_INFO.name='" + TextBox2.Text + "' order by mark desc;select *from #aa;drop table #aa;";
                    try
                    {
                        if (Execute(sql) != -1)
                        {
                            ShowDataList();
                        }
                        else
                        {
                            Alert("查询数据不存在");
                        }
                    }
                    catch
                    { 
                    
                    }
                }
               
            }
            else if(DL4.Text == "账号查询")//尚未实现学生名次
            {
                if (TextBox2.Text == "")
                {
                    Alert("请输入课程名");
                }
                else if (TextBox3.Text == "")
                {
                    Alert("请输入学生账号");
                }
                else
                {
                    sql = "select courseid,COURSE_INFO.name as coursename,studentid,STU_INFO.name as studentname,mark,SEM_INFO.name as semester from SEM_INFO,CLASS_INFO,COURSE_INFO,STU_INFO where CLASS_INFO.courseid=COURSE_INFO.id and COURSE_INFO.teacherid='"+accnum+"'and CLASS_INFO.studentid=STU_INFO.id and CLASS_INFO.semesterid=SEM_INFO.id and CLASS_INFO.state='1' and STU_INFO.id='"+TextBox3.Text+"' and SEM_INFO.name='" + DL3.Text + "' and COURSE_INFO.name='" + TextBox2.Text + "';";
                    try
                    {
                        if (Execute(sql) != -1)
                        {
                            ShowDataList();
                        }
                        else
                        {
                            Alert("查询数据不存在");
                        }
                    }
                    catch
                    {

                    }
                }
            }
            else if (DL4.Text == "班级查询")//已实现查询名次
            {
                if (DL5.Text == "请选择班级")
                {
                    Alert("请选择班级");
                }
                else
                {
                    string cla="";
                    if(DL5.Text=="一班")
                    {
                        cla = "1";
                    }
                     else if(DL5.Text=="二班")
                    {
                        cla = "2";
                    }
                    else if(DL5.Text=="三班")
                    {
                        cla = "3";
                    }

                    sql = "select identity(int,1,1) as rank,courseid,COURSE_INFO.name as coursename,studentid,STU_INFO.name as studentname,STU_INFO.class as class,mark,SEM_INFO.name as semester into #aa from SEM_INFO,CLASS_INFO,COURSE_INFO,STU_INFO where STU_INFO.class='" + cla + "' and CLASS_INFO.courseid=COURSE_INFO.id and COURSE_INFO.teacherid='" + accnum + "' and CLASS_INFO.studentid=STU_INFO.id and CLASS_INFO.semesterid=SEM_INFO.id and CLASS_INFO.state='1' and SEM_INFO.name='" + DL3.Text + "' and COURSE_INFO.name='" + TextBox2.Text + "'order by mark desc;select *from #aa;drop table #aa;";
                    try
                    {
                        if (Execute(sql) != -1)
                        {
                            ShowDataList();
                        }
                        else
                        {
                            Alert("查询数据不存在");
                        }
                    }
                    catch
                    {

                    }
                }

            }
        }
        

    }
    protected void LinkButton5_Click(object sender, EventArgs e)//点击首页处理
    {

    }
    void Alert(string message)//用于测试，无实际意义
    {
        Response.Write("<script>alert('" + message + "')</script>");
    }

}
